package org.xx.armory.service.security.impl;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xx.armory.bindings.Param;
import org.xx.armory.commons.NameObject;
import org.xx.armory.db.DbRow;
import org.xx.armory.db.ManyToOneMapper;
import org.xx.armory.db.Mapper;
import org.xx.armory.db.MatchType;
import org.xx.armory.db.ParameterType;
import org.xx.armory.db.Session;
import org.xx.armory.db.SqlStringBuilder;
import org.xx.armory.rpc.Transactional;
import org.xx.armory.security.Role;
import org.xx.armory.security.User;
import org.xx.armory.service.ServiceException;
import org.xx.armory.service.security.UserPermProvider;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.Optional;

import static org.apache.commons.lang3.StringUtils.isWhitespace;
import static org.apache.commons.lang3.StringUtils.join;
import static org.apache.commons.lang3.StringUtils.trimToEmpty;
import static org.xx.armory.commons.Validators.notBlank;
import static org.xx.armory.commons.Validators.notNull;

public abstract class AbstractDbUserPermProvider
        extends AbstractUserPermProvider
        implements UserPermProvider {
    private final Logger logger = LoggerFactory.getLogger(AbstractDbUserPermProvider.class);

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public final boolean validateUser(
            @Param("user-name") String userName,
            @Param("password") String password
    ) {
        try (final Session session = getSessionFactory().newSession()) {
            return session.newParameter("u_name").setSize(100).setValue(userName).then()
                          .newParameter("u_password").setSize(100).setValue(hashString(password)).then()
                          .newParameter("u_enabled").setValue(true).then()
                          .querySingle("SELECT CASE WHEN EXISTS"
                                               + "(SELECT * FROM MY_USER WHERE U_NAME = #u_name# AND U_PWD = #u_password# AND U_ENABLED = #u_enabled#) "
                                               + "THEN 1 ELSE 0 END FROM DUAL",
                                       Mapper::toInt)
                          .orElse(0) != 0;
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public final Optional<User> loadUserByName(
            @Param("user-name") String userName
    ) {
        try (final Session session = getSessionFactory().newSession()) {
            final SqlStringBuilder sql = new SqlStringBuilder("\n");
            sql.append("SELECT U_NAME, U.U_CREATE_DATE, U.U_LAST_MODIFIED_DATE, U.U_ENABLED, UR.R_NAME");
            sql.append("FROM MY_USER U");
            sql.append("LEFT JOIN MY_USER_ROLE UR USING (U_NAME)");
            sql.append("WHERE U_NAME = #u_name#");

            final Optional<User> result = session.newParameter("u_name").setSize(100)
                                                 .setValue(userName).then()
                                                 .query(sql.build(), new UserMapper())
                                                 .stream()
                                                 .findFirst();

            result.ifPresent(u -> session.newParameter("u_name").setSize(100)
                                         .setValue(userName).then()
                                         .query("SELECT P_KEY, P_TYPE, P_VALUE FROM MY_USER_PROP WHERE U_NAME = #u_name#", this::mapToUserProp)
                                         .stream()
                                         .filter(Objects::nonNull)
                                         .forEach(u::add));

            return result;
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public Optional<Role> loadRoleByName(
            @Param("role-name") String roleName
    ) {
        try (final Session session = getSessionFactory().newSession()) {
            return session.newParameter("r_name").setSize(100).setValue(roleName).then()
                          .querySingle("SELECT R_NAME, R_TITLE, R_DESCRIPTION, R_ENABLED FROM MY_ROLE WHERE R_NAME = #r_name#", this::mapToRole);
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public List<User> findUsers(
            @Param("keyword") String keyword,
            @Param("role-name") String roleName,
            @Param("enabled") Boolean enabled,
            @Param("start-row-index") int startRowIndex,
            @Param("maximum-rows") int maximumRows
    ) {
        keyword = trimToEmpty(keyword);
        roleName = trimToEmpty(roleName);

        try (final Session session = getSessionFactory().newSession()) {
            final SqlStringBuilder sql = new SqlStringBuilder("\n");
            sql.append("SELECT U_NAME, U.U_CREATE_DATE, U.U_LAST_MODIFIED_DATE, U.U_ENABLED, UR.R_NAME");
            sql.append("FROM MY_USER U");
            sql.append("LEFT JOIN MY_USER_ROLE UR USING (U_NAME)");
            createFindUsersSqlCriteria(sql, keyword, roleName, enabled);
            sql.append("ORDER BY U_NAME");

            return new ArrayList<>(session.newParameter("keyword").setSize(100).setMatch(MatchType.CONTAINS).setValue(keyword).then()
                                          .newParameter("r_name").setSize(100).setMatch(MatchType.EXACT).setValue(roleName).then()
                                          .newParameter("u_enabled").setValue(enabled).then()
                                          .setStartRowIndex(startRowIndex)
                                          .setMaximumRows(maximumRows)
                                          .query(sql.build(), new UserMapper()));
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public int findUsersCount(
            @Param("keyword") String keyword,
            @Param("role-name") String roleName,
            @Param("enabled") Boolean enabled
    ) {
        keyword = trimToEmpty(keyword);
        roleName = trimToEmpty(roleName);

        try (final Session session = getSessionFactory().newSession()) {
            final SqlStringBuilder sql = new SqlStringBuilder("\n");
            sql.append("SELECT COUNT(DISTINCT(U_NAME))");
            sql.append("FROM MY_USER U");
            sql.append("LEFT JOIN MY_USER_ROLE UR USING (U_NAME)");
            sql.append("LEFT JOIN MY_USER_PROP UP USING (U_NAME)");
            createFindUsersSqlCriteria(sql, keyword, roleName, enabled);

            return session.newParameter("keyword").setSize(100).setMatch(MatchType.CONTAINS).setValue(keyword).then()
                          .newParameter("r_name").setSize(100).setMatch(MatchType.EXACT).setValue(roleName).then()
                          .newParameter("u_enabled").setValue(enabled).then()
                          .querySingle(sql.build(), Mapper::toInt)
                          .orElse(0);
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public List<User> findAllUsers() {
        try (final Session session = getSessionFactory().newSession()) {
            final String sql = "SELECT U_NAME, U.U_CREATE_DATE, U.U_LAST_MODIFIED_DATE, U.U_ENABLED, UR.R_NAME\n" +
                    " FROM MY_USER U\n" +
                    " LEFT JOIN MY_USER_ROLE UR USING (U_NAME)\n" +
                    " ORDER BY U_NAME\n";
            return session.query(sql, new UserMapper());
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public List<Role> findRoles(
            @Param("keyword") String keyword,
            @Param("enabled") Boolean enabled,
            @Param("start-row-index") int startRowIndex,
            @Param("maximum-rows") int maximumRows
    ) {
        try (final Session session = getSessionFactory().newSession()) {
            final StringBuilder sql = new StringBuilder("SELECT R_NAME, R_TITLE, R_DESCRIPTION, R_ENABLED FROM MY_ROLE\n");
            final StringBuilder sqlCriteria = new StringBuilder();
            if (keyword != null && keyword.length() != 0) {
                sqlCriteria.append("(R_NAME LIKE #keyword#"
                                           + " OR R_TITLE LIKE #keyword#"
                                           + " OR R_DESCRIPTION LIKE #keyword#)\n");
            }
            if (enabled != null) {
                if (sqlCriteria.length() != 0) {
                    sqlCriteria.append(" AND ");
                }
                sqlCriteria.append("R_ENABLED = #r_enabled#\n");
            }
            if (sqlCriteria.length() != 0) {
                sql.append(" WHERE ");
            }
            sql.append(sqlCriteria.toString());
            sql.append("  ORDER BY R_NAME");
            return session.newParameter("keyword").setSize(100).setMatch(MatchType.CONTAINS).setValue(keyword).then()
                          .newParameter("r_enabled").setValue(enabled).then()
                          .setStartRowIndex(startRowIndex)
                          .setMaximumRows(maximumRows)
                          .query(sql.toString(), this::mapToRole);
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public int findRolesCount(
            @Param("keyword") String keyword,
            @Param("enabled") Boolean enabled
    ) {
        try (final Session session = getSessionFactory().newSession()) {
            final StringBuilder sql = new StringBuilder(
                    "SELECT COUNT(R_NAME) FROM MY_ROLE\n");
            final StringBuilder sqlCriteria = new StringBuilder();
            if (keyword != null && keyword.length() != 0) {
                sqlCriteria.append("(R_NAME LIKE #keyword#"
                                           + " OR R_TITLE LIKE #keyword#"
                                           + " OR R_DESCRIPTION LIKE #keyword#)\n");
            }
            if (enabled != null) {
                if (sqlCriteria.length() != 0) {
                    sqlCriteria.append(" AND ");
                }
                sqlCriteria.append("R_ENABLED = #r_enabled#\n");
            }
            if (sqlCriteria.length() != 0) {
                sql.append(" WHERE ");
            }
            sql.append(sqlCriteria.toString());

            return session
                    .newParameter("keyword").setSize(100)
                    .setMatch(MatchType.CONTAINS).setValue(keyword).then()
                    .newParameter("r_enabled")
                    .setValue(enabled).then()
                    .querySingle(sql.toString(), Mapper::toInt)
                    .orElse(0);
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public List<String> findRolesByUser(
            @Param("user-name") String userName
    ) {
        try (final Session session = getSessionFactory().newSession()) {
            return session.newParameter("u_name").setSize(100).setValue(userName).then()
                          .query("SELECT R_NAME FROM MY_USER_ROLE WHERE U_NAME = #u_name# ORDER BY R_NAME", Mapper::toStr);
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public List<String> findUsersByRole(
            @Param("role-name") String roleName
    ) {
        try (final Session session = getSessionFactory().newSession()) {
            return session.newParameter("r_name").setSize(100).setValue(roleName).then()
                          .query("SELECT U_NAME FROM MY_USER_ROLE WHERE R_NAME = #r_name# ORDER BY U_NAME", Mapper::toStr);
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public List<Long> findPermsByRole(
            @Param("role-name") String roleName
    ) {
        try (final Session session = getSessionFactory().newSession()) {
            return session.newParameter("r_name").setSize(100).setValue(roleName).then()
                          .query("SELECT PERM_ID FROM MY_ROLE_PERM WHERE R_NAME = #r_name#", Mapper::toLong);
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public boolean isUserInRole(
            @Param("user-name") String userName,
            @Param("role-name") String roleName
    ) {
        try (final Session session = getSessionFactory().newSession()) {
            return session.newParameter("u_name").setSize(100).setValue(userName).then()
                          .newParameter("r_name").setSize(100).setValue(roleName).then()
                          .querySingle("SELECT CASE WHEN EXISTS"
                                               + "(SELECT U_NAME FROM MY_USER_ROLE WHERE U_NAME = #u_name# AND R_NAME = #r_name#) "
                                               + "THEN 1 ELSE 0 END FROM DUAL", Mapper::toInt)
                          .orElse(0) != 0;
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public boolean isPermitted(
            @Param("user-name") String userName,
            @Param("permissions") int[] permissions
    ) {
        if (permissions == null || permissions.length == 0) {
            return true;
        }
        try (final Session session = getSessionFactory().newSession()) {
            return session.newParameter("u_name").setSize(100).setValue(userName).then()
                          .newParameter("perms").setValue(join(permissions, ',')).then()
                          .newParameter("r_enabled").setValue(true).then()
                          .querySingle("SELECT CASE WHEN EXISTS\n"
                                               + "(SELECT U_NAME FROM MY_USER_ROLE UR\n"
                                               + "  INNER JOIN MY_ROLE R ON R.R_NAME = UR.R_NAME"
                                               + "  INNER JOIN MY_ROLE_PERM RP ON RP.R_NAME = UR.R_NAME\n"
                                               + "WHERE UR.U_NAME = #u_name# AND R.R_ENABLED = #r_enabled#\n"
                                               + "AND RP.PERM_ID IN ($perms$)) \n"
                                               + "THEN 1 ELSE 0 END FROM DUAL", Mapper::toInt)
                          .orElse(0) != 0;
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public boolean isUserExists(
            @Param("user-name") String userName
    ) {
        try (final Session session = getSessionFactory().newSession()) {
            return session.newParameter("u_name").setSize(100).setValue(userName).then()
                          .querySingle("SELECT CASE WHEN EXISTS"
                                               + "(SELECT U_NAME FROM MY_USER WHERE U_NAME = #u_name#) "
                                               + "THEN 1 ELSE 0 END FROM DUAL", Mapper::toInt)
                          .orElse(0) != 0;
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public boolean isRoleExists(
            @Param("role-name") String roleName
    ) {
        try (final Session session = getSessionFactory().newSession()) {
            return session.newParameter("r_name").setSize(100).setValue(roleName).then()
                          .querySingle("SELECT CASE WHEN EXISTS"
                                               + "(SELECT R_NAME FROM MY_ROLE WHERE R_NAME = #r_name#) "
                                               + "THEN 1 ELSE 0 END FROM DUAL", Mapper::toInt)
                          .orElse(0) != 0;
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional
    public String addUser(
            User user
    ) {
        notNull(user, "user");

        final String password = newPassword();

        try (final Session session = getSessionFactory().newSession()) {
            final Date now = new Date();
            final int c = session.newParameter("u_name").setSize(100).setValue(user.getName()).then()
                                 .newParameter("u_password").setSize(100).setValue(hashString(password)).then()
                                 .newParameter("u_create_date").setValue(now).then()
                                 .newParameter("u_last_modified_date").setValue(now).then()
                                 .newParameter("u_enabled").setValue(user.isEnabled()).then()
                                 .update("INSERT INTO MY_USER (U_NAME, U_PWD, U_CREATE_DATE, U_LAST_MODIFIED_DATE, U_ENABLED) "
                                                 + "VALUES (#u_name#, #u_password#, #u_create_date#, #u_last_modified_date#, #u_enabled#)");
            if (c == 0) {
                return "";
            }

            insertUserProps(session, user);

            return password;
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional(readOnly = false)
    public int removeUser(
            String userName
    ) {
        try (final Session session = getSessionFactory().newSession()) {
            return session.newParameter("u_name").setValue(userName).setSize(100).then()
                          .update("DELETE FROM MY_USER WHERE U_NAME = #u_name#");
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    // ---------------------- Update methods --------------------

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional(readOnly = false)
    public int updateUser(
            User user
    ) {
        notNull(user, "user");

        try (final Session session = getSessionFactory().newSession()) {
            final Date now = new Date();

            final int c = session
                    .newParameter("u_name")
                    .setValue(user.getName()).setSize(100).then()
                    .newParameter("u_enabled").setValue(user.isEnabled()).then()
                    .newParameter("u_last_modified_date").setValue(now).then()
                    .update("UPDATE MY_USER SET U_ENABLED = #u_enabled#, U_LAST_MODIFIED_DATE = #u_last_modified_date# WHERE U_NAME = #u_name#");

            session
                    .newParameter("u_name")
                    .setValue(user.getName()).setSize(100).then()
                    .update("DELETE FROM MY_USER_PROP WHERE U_NAME = #u_name#");

            insertUserProps(session, user);
            return c;
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional(readOnly = false)
    public int updatePassword(
            String userName,
            String newPassword
    ) {
        userName = trimToEmpty(userName);
        newPassword = trimToEmpty(newPassword);

        try (final Session session = getSessionFactory().newSession()) {
            return session.newParameter("u_name").setSize(100).setValue(userName).then()
                          .newParameter("u_password").setSize(100).setValue(hashString(newPassword)).then()
                          .update("UPDATE MY_USER SET U_PWD = #u_password# WHERE U_NAME = #u_name#");
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional(readOnly = false)
    public int addRole(
            Role role
    ) {
        notNull(role, "role");

        try (final Session session = getSessionFactory().newSession()) {
            return session.newParameter("r_name").setValue(role.getName()).setSize(100).then()
                          .newParameter("r_title").setValue(role.getTitle()).setSize(200).then()
                          .newParameter("r_description").setValue(role.getDescription()).setSize(2000).then()
                          .newParameter("r_enabled").setValue(role.isEnabled()).then()
                          .update("INSERT INTO MY_ROLE (R_NAME, R_TITLE, R_DESCRIPTION, R_ENABLED)\n"
                                          + "VALUES (#r_name#, #r_title#, #r_description#, #r_enabled#)");
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional(readOnly = false)
    public int removeRole(
            String roleName
    ) {
        roleName = trimToEmpty(roleName);

        try (final Session session = getSessionFactory().newSession()) {
            return session
                    .newParameter("r_name")
                    .setValue(roleName).setSize(100).then()
                    .update("DELETE FROM MY_ROLE WHERE R_NAME = #r_name#");
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional(readOnly = false)
    public int updateRole(
            Role role
    ) {
        notNull(role, "role");

        try (final Session session = getSessionFactory().newSession()) {
            return session
                    .newParameter("r_name")
                    .setValue(role.getName()).setSize(100).then()
                    .newParameter("r_title")
                    .setValue(role.getTitle()).setSize(200).then()
                    .newParameter("r_description")
                    .setValue(role.getDescription()).setSize(2000).then()
                    .newParameter("r_enabled")
                    .setValue(role.isEnabled()).then()
                    .update("UPDATE MY_ROLE SET R_TITLE = #r_title#, "
                                    + "R_DESCRIPTION = #r_description#, R_ENABLED = #r_enabled# "
                                    + "WHERE R_NAME = #r_name#");
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional(readOnly = false)
    public String resetPassword(
            String userName
    ) {
        final String newPassword = newPassword();
        final int c = updatePassword(userName, newPassword);
        return c != 0 ? newPassword : "";
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional(readOnly = false)
    public int assignRoles(
            String userName,
            String... roleNames
    ) {
        userName = notBlank(userName, "userName").trim();

        try (final Session session = getSessionFactory().newSession()) {
            final Date now = new Date();

            session.newParameter("u_name").setValue(userName).setSize(100).then()
                   .update("DELETE FROM MY_USER_ROLE WHERE U_NAME = #u_name#");

            int c = 0;
            if (roleNames != null && roleNames.length != 0) {
                for (final String roleName : roleNames) {
                    if (roleName == null || isWhitespace(roleName)) {
                        continue;
                    }
                    c += session.newParameter("u_name").setValue(userName).setSize(100).then()
                                .newParameter("r_name").setSize(100).setValue(roleName).then()
                                .update("INSERT INTO MY_USER_ROLE (U_NAME, R_NAME) VALUES (#u_name#, #r_name#)");
                }
            }

            session
                    .newParameter("u_name").setValue(userName).setSize(100).then()
                    .newParameter("u_last_modified_date").setValue(now).then()
                    .update("UPDATE MY_USER SET U_LAST_MODIFIED_DATE = #u_last_modified_date# WHERE U_NAME = #u_name#");

            return c;
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    /**
     * {@inheritDoc}
     */
    @Override
    @Transactional(readOnly = false)
    public int assignPerms(
            String roleName,
            long... permissions
    ) {
        roleName = notBlank(roleName, "roleName").trim();

        try (final Session session = getSessionFactory().newSession()) {
            session.newParameter("r_name").setSize(100).setValue(roleName).then()
                   .update("DELETE FROM MY_ROLE_PERM WHERE R_NAME = #r_name#");

            int c = 0;
            if (permissions != null && permissions.length != 0) {
                for (final long perm : permissions) {
                    c += session.newParameter("r_name").setValue(roleName).setSize(100).then()
                                .newParameter("perm").setValue(perm).then()
                                .update("INSERT INTO MY_ROLE_PERM (R_NAME, PERM_ID) VALUES (#r_name#, #perm#)");
                }
            }
            return c;
        } catch (Exception ex) {
            throw new ServiceException(ex);
        }
    }

    private void createFindUsersSqlCriteria(
            SqlStringBuilder sql,
            String keyword,
            String roleName,
            Boolean enabled
    ) {
        final SqlStringBuilder sqlCriteria = new SqlStringBuilder("\n  AND ");
        if (!keyword.isEmpty()) {
            sqlCriteria.append("(U_NAME LIKE #keyword# OR EXISTS(SELECT 1 FROM MY_USER_PROP UP WHERE UP.U_NAME = U_NAME AND UP.P_VALUE = #keyword#))");
        }
        if (!roleName.isEmpty()) {
            sqlCriteria.append("UR.R_NAME = #r_name#");
        }
        if (enabled != null) {
            sqlCriteria.append("U.U_ENABLED = #u_enabled#");
        }

        if (!sqlCriteria.isEmpty()) {
            sql.append("WHERE " + sqlCriteria.build());
        }
    }

    /**
     * 插入帐户属性字段。
     *
     * @param session
     *         当前数据库会话。
     * @param user
     *         帐户对象。
     * @throws NullPointerException
     *         如果参数 {@code session} 或者 {@code user} 是 {@code null} 。
     */
    private void insertUserProps(
            Session session,
            User user
    ) {
        notNull(session, "session");
        notNull(user, "user");

        for (final NameObject item : user) {
            final String pKey = item.getName();
            final Object object = item.getObject();
            final String pValue;
            final int pType;
            if (object instanceof BigDecimal) {
                pType = PROP_NUMBER_TYPE;
                pValue = ((BigDecimal) object).toPlainString();
            } else if (object instanceof Number) {
                pType = PROP_NUMBER_TYPE;
                pValue = object.toString();
            } else if (object instanceof Boolean) {
                pType = PROP_BOOLEAN_TYPE;
                pValue = object.toString();
            } else {
                pType = PROP_STRING_TYPE;
                pValue = object.toString();
            }
            session.newParameter("u_name").setSize(100).setValue(user.getName()).then()
                   .newParameter("p_key").setSize(100).setValue(pKey).then()
                   .newParameter("p_value").setSize(100).setValue(pValue).then()
                   .newParameter("p_type").setType(ParameterType.INTEGER).setValue(pType).then()
                   .update("INSERT INTO MY_USER_PROP (U_NAME, P_KEY, P_VALUE, P_TYPE) VALUES (#u_name#, #p_key#, #p_value#, #p_type#)");
        }
    }

    private NameObject mapToUserProp(
            DbRow row
    ) {
        final String key = trimToEmpty(row.getString(0));
        final int type = row.getInteger(1);
        final String value = row.getString(2);

        if (key.isEmpty()) {
            return null;
        }
        if (value == null || value.isEmpty()) {
            return null;
        }

        switch (type) {
            case PROP_STRING_TYPE:
                return new NameObject(key, value);
            case PROP_NUMBER_TYPE:
                try {
                    return new NameObject(key, new BigDecimal(value));
                } catch (NumberFormatException ex) {
                    logger.warn("cannot read decimal: " + value, ex);
                    return null;
                }
            case PROP_BOOLEAN_TYPE:
                return new NameObject(key, Boolean.parseBoolean(value));
            default:
                LoggerFactory.getLogger(UserPermProvider.class).warn("illegal property type: " + value);
                return null;
        }
    }

    private Role mapToRole(
            DbRow row
    ) {
        notNull(row, "row");

        return new Role(row.getString(0), row.getString(1), row.getString(2), row.getBoolean(3));
    }

    /**
     * @author Haart
     */
    private static class UserMapper
            extends ManyToOneMapper<String, String, User> {

        @Override
        protected User createResult(
                String key,
                Object[] other,
                Collection<String> children
        ) {
            return new User(key, (Date) other[0], (Date) other[1], (Boolean) other[2], children, null);
        }

        @Override
        protected String getKey(
                DbRow row
        ) {
            return row.getString(0);
        }

        @Override
        protected Object[] getOthers(
                DbRow row
        ) {
            return new Object[]{
                    row.getDate(1), row.getDate(2), row.getBoolean(3)
            };
        }

        @Override
        protected String getChild(
                DbRow row
        ) {
            return row.getString(4);
        }
    }
}
